# -*- coding: utf-8 -*-

import cx_Oracle

import traceback
from py27_db import db_execute, db_fetchone, db_fetchall

def oracle_connect(host="127.0.0.1", port=1521, user="chench", password="chench", sid="orcl"):
    """连接数据库oracle"""
    try:
        dsn = cx_Oracle.makedsn(host, port, sid)
        connect = cx_Oracle.connect(user, password, dsn)
        return connect
    except Exception as e:
        print("error:", traceback.format_exc(e))
    return None



def oracle_callproc(connect, sp_name, params_):
    """oracle数据库调用存储过程使用CURSOR返回数据"""
    try:
        cursor = connect.cursor()
        cursor_ = cursor.var(cx_Oracle.CURSOR)
        (params_, cursor_) = cursor.callproc(sp_name, (params_, cursor_))
        column_list = [x[0] for x in cursor_.description]  # 列名
        result_list = list(cursor_)  # 结果
        return column_list, result_list
    except Exception as e:
        print("error:", traceback.format_exc(e))
        pass
    finally:
        cursor.close()
        pass
    pass


if __name__ == '__main__':
    
    connect = oracle_connect(host="127.0.0.1", port=1521, user="chench", password="chench", sid="orcl")
    
    sql_str = "select sysdate from dual"
    cursor = db_execute(connect=connect, sql_str=sql_str)
    result = db_fetchone(cursor=cursor)
    print('result:', result)    
    
    sql_str = "select * from  t_user"
    cursor = db_execute(connect=connect, sql_str=sql_str)
    result = db_fetchall(cursor=cursor)
    print('result:', result)

    sp_name = 'sp_user'
    params = ""
    column_list, result_list = oracle_callproc(connect, sp_name, params)
    print("column_list:", column_list)
    print("result_list:", result_list)

    pass
